package Dao;

import model.Student;
import org.apache.commons.beanutils.BeanUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

public class JdbcTools {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        //I/O
        InputStream resourceAsStream = JdbcTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");

        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        //释放资源
        if (resourceAsStream != null) {
            try {
                resourceAsStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 1.完成数据库链接操作
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

//    @Test
    public void study()
    {
        System.out.println("111 = " + 111);
    }
    /**
     * 2.获取完整的预编译好的sql对象
     *
     * @param sql        预编译的sql语句
     * @param connection db链接对象
     * @param parameters sql替换的参数
     * @return 预编译好的sql对象
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
        //1.获取元数据
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        //2.获取字符串需要的参数个数
        int count = parameterMetaData.getParameterCount();
        //3.判断是否传参，并且设置参数值
        if (count != 0 && parameters != null && parameters.length == count)
            for (int i = 0; i < count; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
        return statement;
    }

    /**
     * 3.实现更新
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int update1(String sql, Object... parameters) throws SQLException {
        Connection connection = JdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 4. 关闭对象，释放资源
     * @param resources
     */
    public static void closeAll(AutoCloseable... resources) {
        if (resources != null && resources.length > 0) {
            Arrays.stream(resources).forEach(source -> {
                if (source != null) {
                    System.out.println("source = " + source);
                    try {
                        source.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            });
        }
    }


    /**
     * 5.根据条件查询一条数据,以传入的javaBean类型返回 null
     *
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;//初始值
        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        System.out.println("metaData = " + metaData);

        if(resultSet.next())//false
        {
            t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
            int count = metaData.getColumnCount();
            for (int i = 1; i <= count; i++) {
                BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
            }

            JdbcTools.closeAll(connection, preparedStatement, resultSet);
            return t;
        }
        return t;
    }

    /**
     * 6.根据条件查询多条数据
     *
     * @param sql        String
     * @param cls        Class<T>
     * @param parameters 替换函数
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */

    public static <T> List<T> queryBeanList(String sql, Class<T> cls, Object... parameters) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {

        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        ArrayList<T> arrayList = new ArrayList<>();
        T t = null;
        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        System.out.println("metaData = " + metaData);
        int count = metaData.getColumnCount();

        while (resultSet.next()) {
            t = cls.newInstance();//实例化一个bean的空对象，用来封装数据

            for (int i = 1; i <= count; i++) {
                BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
            }
            arrayList.add(t);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return arrayList;
    }

    /**
     * 7.查询多条记录，以Map集合的形式返回。
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static ArrayList<Map> queryMapList(String sql, Object... parameters) throws SQLException {
        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        ArrayList<Map> maps = new ArrayList<>();
        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();

        while (resultSet.next()) {
            HashMap<String, Object> map = new HashMap<>();
            for (int i = 1; i <= count; i++) {
                map.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
            maps.add(map);
        }

        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return maps;
    }

    /**
     * 8.查询一条记录，以数组形式返回
     *
     * @param sql
     * @param parameters
     * @return Array
     * @throws SQLException
     */
    public static Object[] queryArray(String sql, Object... parameters) throws SQLException {
        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();

        Object[] o = new Object[count];//定义长度为count的空数组
        resultSet.next();
        for (int i = 0; i < count; i++) {
            o[i] = resultSet.getObject(i + 1);
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return o;

    }


    /**
     * 9.查询多条记录，以数组集合（集合中的数据是数组）的数据状态返回
     *
     * @param sql        sql查询语句
     * @param parameters 替换参数
     * @return List
     * @throws SQLException
     */
    public static List<Object[]> queryArrayList(String sql, Object... parameters) throws SQLException {
        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<Object[]> objects = new ArrayList<>();
        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();

        while (resultSet.next()) {
            Object[] o = new Object[count];//定义长度为count的空数组
            for (int i = 0; i < count; i++) {
                o[i] = resultSet.getObject(i + 1);
            }
            objects.add(o);
        }

        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return objects;
    }


    /**
     * 10.查询一条记录，以键值对形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map<String,Object> queryMap(String sql, Object... parameters) throws SQLException {
        //1.定义需要的变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        //2.获取链接
        connection = JdbcTools.getConnection();
        //3.获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4.执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5.反编译对象，从结果集中获取表的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        HashMap<String, Object> map = new HashMap<>();
        while (resultSet.next()) {
            for (int i = 1; i <= count; i++) {
                map.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
        }
        JdbcTools.closeAll(connection, preparedStatement, resultSet);
        return map;
    }


    /**
     * 11.更新
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int del(String sql, Object... parameters) throws SQLException {
        Connection connection = JdbcTools.getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
        int i = preparedStatement.executeUpdate();
        return i;
    }

    public static void main(String[] args) {
        try {
            List<Student> students = JdbcTools.queryBeanList("select * from student", Student.class);
            System.out.println("students = " + students);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }
}
